package com.house.framework.utils;

import com.alibaba.fastjson.JSONArray;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 大量数据导出Excel
 *
 * @author huangyl
 * @date 2019年8月15日
 */
@Slf4j
public class Datas2Excel {

    /**
     * datas2Excel 扩展
     *
     * @param response
     * @param request
     * @param fileName
     * @param excelHeader
     * @param excelbean
     * @param dataArr
     */
    public static void datas2Excel(HttpServletResponse response, HttpServletRequest request, String fileName, String[] excelHeader, String[] excelbean,
                                   JSONArray dataArr, String numName) {
        List<Map<String, Object>> listData = new ArrayList<Map<String, Object>>();
        dataArr.stream().forEach(item -> {
            listData.add((Map<String, Object>) item);
        });
        datas2Excel(response, request, fileName, excelHeader, excelbean, listData, numName);
    }

    /**
     * datas2Excel 模板导出
     *
     * @param response
     * @param request
     * @param fileName  导出文件名
     * @param template  模板文件
     * @param startRow  数据起始行
     * @param excelbean 数据Key 需要对应模板的每一列
     * @param dataArr   数据
     */
    public static void datas2ExceldependonTemplate(HttpServletResponse response, HttpServletRequest request, String fileName, File template, int startRow, String[] excelbean,
                                                   JSONArray dataArr, String numName) {
        List<Map<String, Object>> listData = new ArrayList<Map<String, Object>>();
        dataArr.stream().forEach(item -> {
            listData.add((Map<String, Object>) item);
        });
        datas2ExceldependonTemplate(response, request, fileName, template, startRow, excelbean, listData, numName);
    }

    public static void datas2Excel(HttpServletResponse response, HttpServletRequest request, String fileName, String[] excelHeader, String[] excelbean,
                                   JSONArray dataArr) {
        List<Map<String, Object>> listData = new ArrayList<Map<String, Object>>();
        dataArr.stream().forEach(item -> {
            listData.add((Map<String, Object>) item);
        });
        datas2Excel(response, request, fileName, excelHeader, excelbean, listData, null);
    }

    /**
     * @param response
     * @param request
     * @param fileName    文件名
     * @param excelHeader 表头列名称
     * @param excelbean   数据key
     * @param data        数据
     */
    public static void datas2Excel(HttpServletResponse response, HttpServletRequest request, String fileName, String[] excelHeader, String[] excelbean,
                                   List<Map<String, Object>> data, String numName) {
        SXSSFWorkbook wb;
        Row row;
        Cell cell;
        CellStyle style;
        request.getSession().setAttribute("exportedFlag", "readding");
        try {

            // 创建一个Workbook，对应一个Excel文件
            wb = new SXSSFWorkbook(100);

            // 设置标题样式
            style = ExcelStyle.getTitleStyle(wb);

            // 在Workbook中添加一个sheet,对应Excel文件中的sheet
            SXSSFSheet sheet = wb.createSheet(fileName);

            // 标题数组
            String[] titleArray = new String[excelHeader.length];
            // 字段名数组
            String[] fieldArray = new String[excelHeader.length];
            if (excelbean != null) {
                for (int i = 0; i < excelbean.length; i++) {

                    fieldArray[i] = excelbean[i];
                }
            }
            for (int i = 0; i < excelHeader.length; i++) {

                titleArray[i] = excelHeader[i];
            }

            if (data == null) {
                // 在sheet中添加标题Key
                row = sheet.createRow((int) 0);// 行数从0开始
                cell = row.createCell(0);// cell列 从0开始 第一列添加序号
                cell.setCellValue("");
                cell.setCellStyle(style);
                sheet.trackAllColumnsForAutoSizing();
                sheet.autoSizeColumn(0);// 自动设置宽度()隐藏Key不自动适应宽度

                // 为列设置Key赋值
                for (int i = 0; i < titleArray.length; i++) {
                    cell = row.createCell(i + 1);// 0号位被序号占用，所以需+1
                    cell.setCellValue("");
                    cell.setCellStyle(style);
                    sheet.autoSizeColumn(i + 1);// 0号位被序号占用，所以需+1
                }
                row.setZeroHeight(true);//隐藏列Key


                // 在sheet中添加标题行
                row = sheet.createRow((int) 1);// 行数从0开始
                cell = row.createCell(0);// cell列 从0开始 第一列添加序号

                cell.setCellValue((null != numName && StringUtils.isNotEmpty(numName)) ? numName : "序号");
                cell.setCellStyle(style);
                sheet.trackAllColumnsForAutoSizing();
                sheet.autoSizeColumn(1);// 自动设置宽度

                // 为标题行赋值
                for (int i = 0; i < titleArray.length; i++) {
                    cell = row.createCell(i + 1);// 0号位被序号占用，所以需+1
                    cell.setCellValue(titleArray[i]);
                    cell.setCellStyle(style);
                    sheet.autoSizeColumn(i + 1);// 0号位被序号占用，所以需+1
                }
            } else {
                // 在sheet中添加标题行
                row = sheet.createRow((int) 0);// 行数从0开始
                cell = row.createCell(0);// cell列 从0开始 第一列添加序号

                cell.setCellValue((null != numName && StringUtils.isNotEmpty(numName)) ? numName : "序号");
                cell.setCellStyle(style);
                sheet.trackAllColumnsForAutoSizing();
                sheet.autoSizeColumn(0);// 自动设置宽度

                // 为标题行赋值
                for (int i = 0; i < titleArray.length; i++) {
                    cell = row.createCell(i + 1);// 0号位被序号占用，所以需+1
                    cell.setCellValue(titleArray[i]);
                    cell.setCellStyle(style);
                    sheet.autoSizeColumn(i + 1);// 0号位被序号占用，所以需+1
                }
            }


            // 数据样式 因为标题和数据样式不同 需要分开设置 不然会覆盖
            style = ExcelStyle.getDataStyle(wb);

            // 遍历集合数据，产生数据行
            int index = 0;
            String cellValue = "";

            if (data == null) {
                index = 1;
            }

            if (data != null && data.size() != 0) {
                // 循环集合
                for (Map<String, Object> info : data) {
                    index++;
                    row = sheet.createRow(index);
                    cell = row.createCell(0);// 序号值永远是第0列
                    cell.setCellValue(index);
                    cell.setCellStyle(style);
                    for (int i = 0; i < fieldArray.length; i++) {
                        cell = row.createCell(i + 1);
                        cell.setCellStyle(style);
                        /*----判断是否为空----*/
                        cellValue = String.valueOf(info.get(fieldArray[i]));
                        if (StringUtils.isBlank(cellValue) || "null".equals(cellValue)/*||"0".equals(cellValue)*/) {
                            cellValue = "";
                        }
                        cell.setCellValue(cellValue);// 实现了动态封装数据
                    }
                }
            }
            //设置自动列宽
            for (int i = 0; i < titleArray.length; i++) {
                sheet.autoSizeColumn(i);
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 16 / 10);
            }
            if (data == null) {
                // 在sheet中添加标题Key
                row = sheet.createRow((int) 0);// 行数从0开始
                cell = row.createCell(0);// cell列 从0开始 第一列添加序号
                cell.setCellValue("");
                cell.setCellStyle(style);
                sheet.trackAllColumnsForAutoSizing();
                //sheet.autoSizeColumn(0);// 自动设置宽度()隐藏Key不自动适应宽度

                // 为列设置Key赋值
                for (int i = 0; i < fieldArray.length; i++) {
                    cell = row.createCell(i + 1);// 0号位被序号占用，所以需+1
                    cell.setCellValue(fieldArray[i]);
                    cell.setCellStyle(style);
                    //sheet.autoSizeColumn(i + 1);// 0号位被序号占用，所以需+1
                }
                row.setZeroHeight(true);//隐藏列Key
            }

            // 导出（下载）Excel
            outExcel(response, request, fileName, wb);
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

    /**
     * 使用现有模板导出数据
     *
     * @param response
     * @param request
     * @param startRow  数据起始行
     * @param fileName  文件名
     * @param excelbean 数据key
     * @param datas     数据
     */
    public static void datas2ExceldependonTemplateMany(HttpServletResponse response, HttpServletRequest request,
                                                       String fileName,
                                                       File template,
                                                       int[] startRow,
                                                       List<String[]> excelbean,
                                                       List<List<Map<String, Object>>> datas,
                                                       String numName) {
        XSSFWorkbook wb;
        Row row;
        Cell cell;
        CellStyle style;

        request.getSession().setAttribute("exportedFlag", "readding");
        try {

            // 创建一个Workbook，对应一个Excel文件
            wb = new XSSFWorkbook(new FileInputStream(template));
//            style = ExcelStyle.getDataStyle(wb);

            style = wb.createCellStyle();
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setAlignment(HorizontalAlignment.CENTER);
            // 在Workbook中添加一个sheet,对应Excel文件中的sheet
//			SXSSFSheet sheet = wb.getSheetAt(0);
            for (int nb = 0; nb < 2; nb++) {
                XSSFSheet sheet = wb.getSheetAt(nb);
                sheet.setForceFormulaRecalculation(true);


//			XSSFRow dataKey = sheet.getRow(startRow);
//			int physicalNumberOfCells = dataKey.getPhysicalNumberOfCells();

                // 字段名数组
                String[] fieldArray = excelbean.get(nb);


//			for (int i = 0; i < physicalNumberOfCells; i++) {
//				XSSFCell cell1 = dataKey.getCell(i);
//				String key = cell1.getStringCellValue().replace("&=Table.","");
//				fieldArray[i]=key;
//			}

                // 遍历集合数据，产生数据行
                int index = startRow[nb];
                String cellValue = "";
                List<Map<String, Object>> data = datas.get(nb);
                if (data != null && data.size() != 0) {

                    XSSFCellStyle styles[] = new XSSFCellStyle[excelbean.get(nb).length];

                    //获取示例样式
                    XSSFRow exampleRow = sheet.getRow(startRow[nb]);
                    for (int i = 0; i < exampleRow.getPhysicalNumberOfCells(); i++) {
                        XSSFCellStyle cellStyle = exampleRow.getCell(i).getCellStyle();
                        cellStyle.getFont().setBold(false);
                        styles[i] = cellStyle;
                    }

                    // 循环集合
                    for (Map<String, Object> info : data) {
                        row = sheet.createRow(index);
                        for (int i = 0; i < fieldArray.length; i++) {
                            cell = row.createCell(i);
//                        设置样式
                            cell.setCellStyle(styles[i]);

                            /*----判断是否为空----*/
                            cellValue = String.valueOf(info.get(fieldArray[i]));
                            if (StringUtils.isBlank(cellValue) || "null".equals(cellValue)/*||"0".equals(cellValue)*/) {
                                cellValue = "";
                            }
                            cell.setCellValue(cellValue);// 实现了动态封装数据
                        }
                        index++;
                    }
                }
                if (data == null) {
                    // 在sheet中添加标题Key
                    row = sheet.createRow((int) 0);// 行数从0开始
                    cell = row.createCell(0);// cell列 从0开始 第一列添加序号
                    cell.setCellStyle(style);
                    cell.setCellValue("");
//				sheet.trackAllColumnsForAutoSizing();
                    sheet.autoSizeColumn(0);// 自动设置宽度()隐藏Key不自动适应宽度

                    // 为列设置Key赋值
                    for (int i = 0; i < fieldArray.length; i++) {
                        cell = row.createCell(i + 1);// 0号位被序号占用，所以需+1
                        cell.setCellValue(fieldArray[i]);
                        //sheet.autoSizeColumn(i + 1);// 0号位被序号占用，所以需+1
                    }
                    row.setZeroHeight(true);//隐藏列Key
                }
            }

            // 导出（下载）Excel
            outExcel(response, request, fileName, wb);

        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

    /**
     * 使用现有模板导出数据
     *
     * @param response
     * @param request
     * @param startRow  数据起始行
     * @param fileName  文件名
     * @param excelbean 数据key
     * @param data      数据
     */
    public static void datas2ExceldependonTemplate(HttpServletResponse response, HttpServletRequest request,
                                                   String fileName,
                                                   File template,
                                                   int startRow,
                                                   String[] excelbean,
                                                   List<Map<String, Object>> data,
                                                   String numName) {
        XSSFWorkbook wb;
        Row row;
        Cell cell;
        CellStyle style;

        request.getSession().setAttribute("exportedFlag", "readding");
        try {

            // 创建一个Workbook，对应一个Excel文件
            wb = new XSSFWorkbook(new FileInputStream(template));
//            style = ExcelStyle.getDataStyle(wb);

            style = wb.createCellStyle();
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setAlignment(HorizontalAlignment.CENTER);
            // 在Workbook中添加一个sheet,对应Excel文件中的sheet
//			SXSSFSheet sheet = wb.getSheetAt(0);
            XSSFSheet sheet = wb.getSheetAt(0);
            sheet.setForceFormulaRecalculation(true);


//			XSSFRow dataKey = sheet.getRow(startRow);
//			int physicalNumberOfCells = dataKey.getPhysicalNumberOfCells();

            // 字段名数组
            String[] fieldArray = excelbean;


//			for (int i = 0; i < physicalNumberOfCells; i++) {
//				XSSFCell cell1 = dataKey.getCell(i);
//				String key = cell1.getStringCellValue().replace("&=Table.","");
//				fieldArray[i]=key;
//			}

            // 遍历集合数据，产生数据行
            int index = startRow;
            String cellValue = "";

            if (data != null && data.size() != 0) {

                XSSFCellStyle styles[] = new XSSFCellStyle[excelbean.length];

                //获取示例样式
                XSSFRow exampleRow = sheet.getRow(startRow);
                for (int i = 0; i < exampleRow.getPhysicalNumberOfCells(); i++) {
                    XSSFCellStyle cellStyle = exampleRow.getCell(i).getCellStyle();
                    cellStyle.getFont().setBold(false);
                    styles[i] = cellStyle;
                }

                // 循环集合
                for (Map<String, Object> info : data) {
                    row = sheet.createRow(index);
                    for (int i = 0; i < fieldArray.length; i++) {
                        cell = row.createCell(i);
//                        设置样式
                        cell.setCellStyle(styles[i]);

                        /*----判断是否为空----*/
                        cellValue = String.valueOf(info.get(fieldArray[i]));
                        if (StringUtils.isBlank(cellValue) || "null".equals(cellValue)/*||"0".equals(cellValue)*/) {
                            cellValue = "";
                        }
                        cell.setCellValue(cellValue);// 实现了动态封装数据
                    }
                    index++;
                }
            }
            if (data == null) {
                // 在sheet中添加标题Key
                row = sheet.createRow((int) 0);// 行数从0开始
                cell = row.createCell(0);// cell列 从0开始 第一列添加序号
                cell.setCellStyle(style);
                cell.setCellValue("");
//				sheet.trackAllColumnsForAutoSizing();
                sheet.autoSizeColumn(0);// 自动设置宽度()隐藏Key不自动适应宽度

                // 为列设置Key赋值
                for (int i = 0; i < fieldArray.length; i++) {
                    cell = row.createCell(i + 1);// 0号位被序号占用，所以需+1
                    cell.setCellValue(fieldArray[i]);
                    //sheet.autoSizeColumn(i + 1);// 0号位被序号占用，所以需+1
                }
                row.setZeroHeight(true);//隐藏列Key
            }

            // 导出（下载）Excel
            outExcel(response, request, fileName, wb);

        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

    /**
     * 污染源档案导出excel
     * 勿动
     *
     * @param response
     * @param request
     * @param startRow  数据起始行
     * @param fileName  文件名
     * @param excelbean 字段名
     * @param data      数据
     * @param cnNames   字段中文名
     */
    public static void pollutionExportExcel(HttpServletResponse response, HttpServletRequest request,
                                            String fileName,
                                            File template,
                                            int startRow,
                                            String[] excelbean,
                                            List<Map<String, Object>> data,
                                            String[] cnNames) {
        XSSFWorkbook wb;
        Row row;
        Cell cell;
        CellStyle style;

        request.getSession().setAttribute("exportedFlag", "readding");
        try {

            // 创建一个Workbook，对应一个Excel文件
            wb = new XSSFWorkbook(new FileInputStream(template));

            style = wb.createCellStyle();
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setAlignment(HorizontalAlignment.CENTER);
            XSSFSheet sheet = wb.getSheetAt(0);
            sheet.setForceFormulaRecalculation(true);

            // 字段名数组
            String[] fieldArray = excelbean;


            String cellValue = "";

            XSSFCellStyle commonStyle = wb.createCellStyle();

            DataFormat format = wb.createDataFormat();
            commonStyle.setDataFormat(format.getFormat("@"));

            if (data != null && data.size() != 0) {

                XSSFRow titleRow = sheet.getRow(0);
                row = sheet.getRow(startRow - 1);

                cell = row.getCell(0);
                style = cell.getCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);
                style.setVerticalAlignment(VerticalAlignment.CENTER);

                for (int i = 0; i < cnNames.length; i++) {
                    sheet.setDefaultColumnStyle(i, commonStyle);
                    cell = row.createCell(i);
                    cell.setCellValue(cnNames[i]);// 实现了动态封装数据
                    cell.setCellStyle(style);
                    sheet.autoSizeColumn(i);

                    cell = titleRow.createCell(i);
                    cell.setCellValue("");
                    cell.setCellStyle(style);
                }
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cnNames.length - 1));
                titleRow.getCell(0).setCellValue("污染源档案");

                // 遍历集合数据，产生数据行
                int index = startRow;
                // 循环集合
                for (Map<String, Object> info : data) {
                    row = sheet.createRow(index);
                    for (int i = 0; i < fieldArray.length; i++) {
                        cell = row.createCell(i);

                        /*----判断是否为空----*/
                        cellValue = String.valueOf(info.get(fieldArray[i]));
                        if (StringUtils.isBlank(cellValue) || "null".equals(cellValue)/*||"0".equals(cellValue)*/) {
                            cellValue = "";
                        }
                        cell.setCellValue(cellValue);// 实现了动态封装数据
                    }
                    index++;
                }
            }

            // 导出（下载）Excel
            outExcel(response, request, fileName, wb);

        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

    public static void datas2ExceldependonTemplateCustomLeft(HttpServletResponse response, HttpServletRequest request,
                                                             String fileName,
                                                             File template,
                                                             int startRow,
                                                             String[] excelbean,
                                                             List<Map<String, Object>> data,
                                                             String numName,
                                                             String[] custom) {
        XSSFWorkbook wb;
        Row row;
        Cell cell;
        CellStyle style;

        request.getSession().setAttribute("exportedFlag", "readding");
        try {

            // 创建一个Workbook，对应一个Excel文件
            wb = new XSSFWorkbook(new FileInputStream(template));
//            style = ExcelStyle.getDataStyle(wb);

            style = wb.createCellStyle();
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setAlignment(HorizontalAlignment.CENTER);

            CellStyle fontLeft = wb.createCellStyle();
            fontLeft.setAlignment(HorizontalAlignment.LEFT);
            fontLeft.setBorderBottom(BorderStyle.THIN);
            fontLeft.setBorderLeft(BorderStyle.THIN);
            fontLeft.setBorderTop(BorderStyle.THIN);
            fontLeft.setBorderRight(BorderStyle.THIN);

            // 在Workbook中添加一个sheet,对应Excel文件中的sheet
//      SXSSFSheet sheet = wb.getSheetAt(0);
            XSSFSheet sheet = wb.getSheetAt(0);

//      XSSFRow dataKey = sheet.getRow(startRow);
//      int physicalNumberOfCells = dataKey.getPhysicalNumberOfCells();

            // 字段名数组
            String[] fieldArray = excelbean;


//      for (int i = 0; i < physicalNumberOfCells; i++) {
//        XSSFCell cell1 = dataKey.getCell(i);
//        String key = cell1.getStringCellValue().replace("&=Table.","");
//        fieldArray[i]=key;
//      }

            // 遍历集合数据，产生数据行
            int index = startRow;
            String cellValue = "";

            if (data != null && data.size() != 0) {
                // 循环集合
                for (Map<String, Object> info : data) {

                    row = sheet.createRow(index);

                    for (int i = 0; i < fieldArray.length; i++) {
                        cell = row.createCell(i);

                        if (custom.length > 0) {
                            for (String s : custom) {
                                if (fieldArray[i].equals(s)) {
                                    cell.setCellStyle(fontLeft);
                                } else {
                                    cell.setCellStyle(style);
                                }
                            }
                        }

                        /*----判断是否为空----*/
                        cellValue = String.valueOf(info.get(fieldArray[i]));
                        if (StringUtils.isBlank(cellValue) || "null".equals(cellValue)/*||"0".equals(cellValue)*/) {
                            cellValue = "";
                        }
                        cell.setCellValue(cellValue);// 实现了动态封装数据
                    }
                    index++;
                }
            }
            if (data == null) {
                // 在sheet中添加标题Key
                row = sheet.createRow((int) 0);// 行数从0开始
                cell = row.createCell(0);// cell列 从0开始 第一列添加序号
                cell.setCellStyle(style);
                cell.setCellValue("");
//        sheet.trackAllColumnsForAutoSizing();
                sheet.autoSizeColumn(0);// 自动设置宽度()隐藏Key不自动适应宽度

                // 为列设置Key赋值
                for (int i = 0; i < fieldArray.length; i++) {
                    cell = row.createCell(i + 1);// 0号位被序号占用，所以需+1
                    cell.setCellValue(fieldArray[i]);
                    //sheet.autoSizeColumn(i + 1);// 0号位被序号占用，所以需+1
                }
                row.setZeroHeight(true);//隐藏列Key
            }

            // 导出（下载）Excel
            outExcel(response, request, fileName, wb);

        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

    /**
     * 使用现有模板导出数据
     *
     * @param response
     * @param request
     * @param startRow  数据起始行
     * @param fileName  文件名
     * @param excelbean 数据key
     * @param data      数据
     */
    public static void datas2ExceldependonTemplate2(HttpServletResponse response, HttpServletRequest request,
                                                    String fileName,
                                                    File template,
                                                    int startRow,
                                                    String[] excelbean,
                                                    List<Map<String, Object>> data,
                                                    String numName,
                                                    String name) {
        XSSFWorkbook wb;
        Row row;
        Cell cell;
        CellStyle style;

        request.getSession().setAttribute("exportedFlag", "readding");
        try {

            // 创建一个Workbook，对应一个Excel文件
            wb = new XSSFWorkbook(new FileInputStream(template));
            style = ExcelStyle.getDataStyle(wb);
            style.setWrapText(true);
            // 在Workbook中添加一个sheet,对应Excel文件中的sheet
//			SXSSFSheet sheet = wb.getSheetAt(0);
            XSSFSheet sheet = wb.getSheetAt(0);


//			XSSFRow dataKey = sheet.getRow(startRow);
//			int physicalNumberOfCells = dataKey.getPhysicalNumberOfCells();

            // 字段名数组
            String[] fieldArray = excelbean;


//			for (int i = 0; i < physicalNumberOfCells; i++) {
//				XSSFCell cell1 = dataKey.getCell(i);
//				String key = cell1.getStringCellValue().replace("&=Table.","");
//				fieldArray[i]=key;
//			}

            // 遍历集合数据，产生数据行
            int index = startRow;
            String cellValue = "";
            XSSFFont font = wb.createFont();
            font.setFontName("等线");
            font.setFontHeightInPoints((short) 11);// 设置字体大小
            if (data != null && data.size() != 0) {
                // 循环集合
                for (Map<String, Object> info : data) {

                    row = sheet.createRow(index);

                    for (int i = 0; i < fieldArray.length; i++) {
                        cell = row.createCell(i);
                        style.setBorderBottom(BorderStyle.THIN);
                        style.setBorderLeft(BorderStyle.THIN);
                        style.setBorderTop(BorderStyle.THIN);
                        style.setBorderRight(BorderStyle.THIN);
                        style.setAlignment(HorizontalAlignment.CENTER);
                        style.setFont(font);

                        CellStyle dataStyle = ExcelStyle.getDataStyle(wb);
                        dataStyle.setBorderBottom(BorderStyle.THIN);
                        dataStyle.setBorderLeft(BorderStyle.THIN);
                        dataStyle.setBorderTop(BorderStyle.THIN);
                        dataStyle.setBorderRight(BorderStyle.THIN);
                        dataStyle.setAlignment(HorizontalAlignment.LEFT);
                        dataStyle.setFont(font);
                        dataStyle.setWrapText(true);

                        if ("F_TESTMECHANISMFULLNAME".equals(fieldArray[i])) {
                            cell.setCellStyle(dataStyle);
                        } else if ("F_SCORECARDQUESTIONS".equals(fieldArray[i])) {
                            cell.setCellStyle(dataStyle);
                        } else if ("F_TestMechanismFullName".equals(fieldArray[i])) {
                            cell.setCellStyle(dataStyle);
                        } else if ("f_testmechanismfullname".equals(fieldArray[i])) {
                            cell.setCellStyle(dataStyle);
                        } else if ("F_Address".equals(fieldArray[i])) {
                            cell.setCellStyle(dataStyle);
                        } else if ("F_ScoreNotes".equals(fieldArray[i])) {
                            cell.setCellStyle(dataStyle);
                        } else if ("QUESTLIST".equals(fieldArray[i])) {
                            cell.setCellStyle(dataStyle);
                        } else {
                            cell.setCellStyle(style);
                        }

                        /*----判断是否为空----*/
                        cellValue = String.valueOf(info.get(fieldArray[i]));
                        if (StringUtils.isBlank(cellValue) || "null".equals(cellValue)/*||"0".equals(cellValue)*/) {
                            cellValue = "";
                        }
                        cell.setCellValue(cellValue);// 实现了动态封装数据
                    }
                    index++;
                }
            }
            if (data == null) {
                // 在sheet中添加标题Key
                row = sheet.createRow((int) 0);// 行数从0开始
                cell = row.createCell(0);// cell列 从0开始 第一列添加序号
                cell.setCellStyle(style);
                cell.setCellValue("");
//				sheet.trackAllColumnsForAutoSizing();
                sheet.autoSizeColumn(0);// 自动设置宽度()隐藏Key不自动适应宽度

                // 为列设置Key赋值
                for (int i = 0; i < fieldArray.length; i++) {
                    cell = row.createCell(i + 1);// 0号位被序号占用，所以需+1
                    cell.setCellValue(fieldArray[i]);
                    //sheet.autoSizeColumn(i + 1);// 0号位被序号占用，所以需+1
                }
                row.setZeroHeight(true);//隐藏列Key
            }

            sheet.getRow(0).getCell(0).setCellValue(name);
            // 导出（下载）Excel
            outExcel(response, request, fileName, wb);

        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

    /**
     * 使用现有模板导出多个sheet的数据
     *
     * @param response
     * @param request
     * @param startRow  数据起始行
     * @param fileName  文件名
     * @param excelbean 数据key
     * @param data      数据
     */
    public static void datas2ExceldependonTemplateMultiplePage(HttpServletResponse response, HttpServletRequest request,
                                                               String fileName,
                                                               File template,
                                                               int startRow,
                                                               String[][] excelbean,
                                                               List<List<Map<String, Object>>> data,
                                                               String numName) {
        XSSFWorkbook wb;
        CellStyle style;

        request.getSession().setAttribute("exportedFlag", "readding");
        try {

            // 创建一个Workbook，对应一个Excel文件
            wb = new XSSFWorkbook(new FileInputStream(template));
//            style = ExcelStyle.getDataStyle(wb);
            style = wb.createCellStyle();
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setAlignment(HorizontalAlignment.CENTER);


            int numberOfSheets = wb.getNumberOfSheets();

            for (int i = 0; i < data.size(); i++) {
                Row row;
                Cell cell;
                List<Map<String, Object>> list = data.get(i);
                XSSFSheet sheet = wb.getSheetAt(i);

                // 字段名数组
                String[] fieldArray = excelbean[i];

                // 遍历集合数据，产生数据行
                int index = startRow;
                String cellValue = "";

                if (list != null && list.size() != 0) {
                    // 循环集合
                    for (Map<String, Object> info : list) {

                        row = sheet.createRow(index);

                        for (int j = 0; j < fieldArray.length; j++) {
                            cell = row.createCell(j);
                            cell.setCellStyle(style);
                            /*----判断是否为空----*/
                            cellValue = String.valueOf(info.get(fieldArray[j]));
                            if (StringUtils.isBlank(cellValue) || "null".equals(cellValue)) {
                                cellValue = "";
                            }
                            cell.setCellValue(cellValue);// 实现了动态封装数据
                        }
                        index++;
                    }
                }
                if (list == null) {
                    // 在sheet中添加标题Key
                    row = sheet.createRow((int) 0);// 行数从0开始
                    cell = row.createCell(0);// cell列 从0开始 第一列添加序号
                    cell.setCellStyle(style);
                    cell.setCellValue("");
//				sheet.trackAllColumnsForAutoSizing();
                    sheet.autoSizeColumn(0);// 自动设置宽度()隐藏Key不自动适应宽度

                    // 为列设置Key赋值
                    for (int j = 0; j < fieldArray.length; j++) {
                        cell = row.createCell(j + 1);// 0号位被序号占用，所以需+1
                        cell.setCellValue(fieldArray[j]);
                        //sheet.autoSizeColumn(i + 1);// 0号位被序号占用，所以需+1
                    }
                    row.setZeroHeight(true);//隐藏列Key
                }


            }
            wb.setActiveSheet(0);
// 导出（下载）Excel
            outExcel(response, request, fileName, wb);
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

    /**
     * @param response
     * @param request
     * @param fileName 文件名
     */
    public static void datas2Excel2(HttpServletResponse response, HttpServletRequest request, String fileName,
                                    TreeMap<Object, List<Map<String, Object>>> treeMap,
                                    List<Map<String, Object>> stations, String dateType) {
        SXSSFWorkbook wb;

        Cell cell;
        CellStyle style;
        request.getSession().setAttribute("exportedFlag", "readding");
        try {

            // 创建一个Workbook，对应一个Excel文件
            wb = new SXSSFWorkbook(100);

            // 设置标题样式


            String[] indexes = "AQI SO₂(微克每立方米) NO₂(微克每立方米) O₃(微克每立方米) CO(毫克每立方米) PM2.5(微克每立方米) PM10(微克每立方米) BC".split(" ");
            String[] indexeCodes = "F_AQIDATA F_SO2CONS F_NO2CONS F_O3CONS F_CO1CONS F_PM25CONS F_PM10CONS F_BCCONS".split(" ");
            for (int sheelIndex = 0; sheelIndex < indexes.length; sheelIndex += 1) {
                // 在Workbook中添加一个sheet,对应Excel文件中的sheet
                SXSSFSheet sheet = wb.createSheet(indexes[sheelIndex]);
                style = ExcelStyle.getTitleStyle(wb);
                int dateStrLen = "3".equals(dateType) ? 10 : 16;
                // 标题数组
                List<String> titleArray = new ArrayList<>();
                titleArray.add("日期");
                titleArray.addAll(stations.stream().map(e -> (String) e.get("NAME")).collect(Collectors.toList()));

                List<String> ids = stations.stream().map(e -> (String) e.get("CODE")).collect(Collectors.toList());
                // 在sheet中添加标题行
                Row row = sheet.createRow((int) 0);// 行数从0开始
                cell = row.createCell(0);// cell列 从0开始 第一列添加序号

                cell.setCellStyle(style);
                sheet.trackAllColumnsForAutoSizing();
                sheet.autoSizeColumn(0);// 自动设置宽度
                // 为标题行赋值
                for (int i = 0; i < titleArray.size(); i += 1) {
                    cell = row.createCell(i);// 0号位被序号占用，所以需+1
                    cell.setCellValue(titleArray.get(i));
                    cell.setCellStyle(style);
                    sheet.autoSizeColumn(i);// 0号位被序号占用，所以需+1
                }
                // 数据样式 因为标题和数据样式不同 需要分开设置 不然会覆盖
                style = ExcelStyle.getDataStyle(wb);

                // 遍历集合数据，产生数据行
                CellStyle finalStyle = style;
                int finalSheelIndex = sheelIndex;
                treeMap.forEach((k, v) -> {

                    Row itemRow = sheet.createRow(sheet.getLastRowNum() + 1);

                    Cell itemCell = itemRow.createCell(0);
                    itemCell.setCellValue(k.toString().substring(0, dateStrLen));
                    itemCell.setCellStyle(finalStyle);
                    for (int i = 0; i < v.size(); i += 1) {
                        Map<String, Object> itemData = v.get(i);

                        itemCell = itemRow.createCell(ids.indexOf((String) itemData.get("ID")) + 1);
                        itemCell.setCellStyle(finalStyle);
                        String value = (String) itemData.get(indexeCodes[finalSheelIndex]);
                        if (value != null && value.matches("[\\d]+")) {
                            itemCell.setCellValue(Double.parseDouble(value));// 实现了动态封装数据
                        }

                    }

                });

                //设置自动列宽
                for (int i = 0; i < titleArray.size(); i += 1) {
                    sheet.autoSizeColumn(i);
                    sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 16 / 10);
                }
            }

            // 导出（下载）Excel
            outExcel(response, request, fileName, wb);
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

    /**
     * 导出（下载）excel表
     *
     * @param response 请求
     * @param fileName Excel文件名
     * @param wb       HSSFWorkbook数据（Excel表）
     */
    public static void outExcel(HttpServletResponse response, HttpServletRequest request, String fileName, SXSSFWorkbook wb) {
        // 打开流
        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            // 设置请求
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            // HSSFWorkbook写入流
            wb.write(outputStream);
            // 刷新流
//			request.getSession().removeAttribute("exportedFlag");
            outputStream.flush();
        } catch (Exception e) {
            log.error(e.getMessage());
        } finally {
            try {
                if (outputStream != null) {
                    // 关闭流
                    outputStream.close();
                    wb.close();
                }
            } catch (Exception e2) {
                log.error(e2.getMessage());
            }
        }
    }

    /**
     * 导出（下载）excel表
     *
     * @param response 请求
     * @param fileName Excel文件名
     * @param wb       HSSFWorkbook数据（Excel表）
     */
    public static void outExcel(HttpServletResponse response, HttpServletRequest request, String fileName, Workbook wb) {
        // 打开流
        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            // 设置请求
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName + (fileName.endsWith(".xlsx") ? "" : ".xlsx"), "UTF-8"));
            // HSSFWorkbook写入流
            wb.write(outputStream);
            // 刷新流
//			request.getSession().removeAttribute("exportedFlag");
            outputStream.flush();
        } catch (Exception e) {
            log.error(e.getMessage());
        } finally {
            try {
                if (outputStream != null) {
                    // 关闭流
                    outputStream.close();
                    wb.close();

                }
            } catch (Exception e2) {
                log.error(e2.getMessage());
            }
        }
    }

    /**
     * ajax导出excel 存储本地临时文件
     *
     * @param fileName  文件名
     * @param sheetName 页签名
     * @param excelbean 数据key
     * @param data      数据
     */
    public static void exportByTemplate(HttpServletRequest request, HttpServletResponse response,
                                        Workbook wb,
                                        String sheetName,
                                        int dataFirstIndex,
                                        String fileName,
                                        String[] excelbean,
                                        List<Map<String, Object>> data) {
        Row row;
        Cell cell;
        CellStyle style;
        try {

            // 在Workbook中添加一个sheet,对应Excel文件中的sheet
            Sheet sheet = wb.getSheet(sheetName);

            // 数据样式 因为标题和数据样式不同 需要分开设置 不然会覆盖
            style = ExcelStyle.getDataStyle(wb);
            style.setWrapText(true);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);

            // 遍历集合数据，产生数据行
            int index = dataFirstIndex;
            String cellValue = "";

            if (data != null && data.size() != 0) {
                for (Map<String, Object> info : data) {

                    row = sheet.createRow(index);
                    for (int i = 0; i < excelbean.length; i++) {

                        cell = row.createCell(i);
                        cell.setCellStyle(style);
                        row.setHeightInPoints(30);
                        /*----判断是否为空----*/
                        cellValue = String.valueOf(info.get(excelbean[i]));
                        /*||"0".equals(cellValue)*/
                        //int i1 = Arrays.binarySearch(new int[]{5, 6, 7, 11, 12, 13, 17, 18, 19, 23, 24, 25, 29, 30, 31}, i);
                        int i1 = -1;
                        if (StringUtils.isBlank(cellValue) || "null".equals(cellValue) || i1 >= 0) {
                            cellValue = "";
                        }
                        // 实现了动态封装数据
                        cell.setCellValue(cellValue);

                    }
                    log.debug("导出第" + (index + 1) + "条数据");
                    index++;
                }
            }
            outExcel(response, request, fileName, wb);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Map<String, String> outExcel_Tem(HttpServletRequest request, String fileName, Workbook wb) {
        // 打开流
        FileOutputStream fileOutputStream = null;
        Map<String, String> rMap = null;
        String serverPath = request.getSession().getServletContext().getRealPath("/") + "/";
        String filePath = "tem/" + System.currentTimeMillis() + ".xlsx";

        try {
            //存储到本地临时文件
            File file = new File(serverPath + filePath);
            File parentFile = file.getParentFile();
            if (!parentFile.exists()) {
                parentFile.mkdirs();
            }
            fileOutputStream = new FileOutputStream(file);
            wb.write(fileOutputStream);
            rMap = new HashMap<String, String>() {
                {
                    put("filePath", filePath);
                    put("fileName", fileName);
                    put("state", "200");
                }
            };
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (fileOutputStream != null) {
                    // 关闭流
                    fileOutputStream.close();
                    wb.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return rMap;
    }

}
